﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using webnew;
using webnew.Model;
using webnew.DBUtility;

/// -------数据访问类：Sys_CompanyHelper
/// -------创建日期：2010年04月08日 02:02
/// -------创建人：

namespace webnew.DAL
{

    public class Sys_CompanyHelper
    {

        private SqlHelper DbHelper;

        public Sys_CompanyHelper()
        {
            DbHelper = new SqlHelper();
        }

        /// <summary>
        /// 新增一个实体
        /// </summary>
        public int Add(Model.Sys_CompanyInfo model)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("INSERT INTO Sys_Company(");
            strSql.Append("Id,compname,compAbb,ERPCode,comptype,ParentID,CompanyLevel,remark,sort,isdel)");
            strSql.Append("VALUES(");
            strSql.Append("@In_Id,@In_compname,@In_compAbb,@In_ERPCode,@In_comptype,@In_ParentID,@In_CompanyLevel,@In_remark,@In_sort,@In_isdel)");
            System.Data.SqlClient.SqlParameter[] CmdParams = new System.Data.SqlClient.SqlParameter[] {
                    DbHelper.CreateInSqlParameter("@In_Id",DbType.AnsiString,Guid.NewGuid().ToString()),
                    DbHelper.CreateInSqlParameter("@In_compname",DbType.AnsiString,model.compname),
                    DbHelper.CreateInSqlParameter("@In_compAbb",DbType.AnsiString,model.compAbb),
                    DbHelper.CreateInSqlParameter("@In_ERPCode",DbType.AnsiString,model.ERPCode),
                    DbHelper.CreateInSqlParameter("@In_comptype",DbType.AnsiString,model.comptype),
                    DbHelper.CreateInSqlParameter("@In_ParentID",DbType.AnsiString,model.ParentID),
                    DbHelper.CreateInSqlParameter("@In_CompanyLevel",DbType.AnsiString,model.CompanyLevel),
                    DbHelper.CreateInSqlParameter("@In_remark",DbType.AnsiString,model.remark),
                    DbHelper.CreateInSqlParameter("@In_sort",DbType.Int32,model.sort),
                    DbHelper.CreateInSqlParameter("@In_isdel",DbType.Int32,model.isdel)};
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), CmdParams);
        }

        /// <summary>
        /// 根据ID修改一个实体
        /// </summary>
        public int Modify(Model.Sys_CompanyInfo model)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("UPDATE Sys_Company SET ");
            strSql.Append("compname = @In_compname,");
            strSql.Append("compAbb = @In_compAbb,");
            strSql.Append("ERPCode = @In_ERPCode,");
            strSql.Append("comptype = @In_comptype,");
            strSql.Append("ParentID = @In_ParentID,");
            strSql.Append("CompanyLevel = @In_CompanyLevel,");
            strSql.Append("remark = @In_remark,");
            strSql.Append("sort = @In_sort,");
            strSql.Append("isdel = @In_isdel");
            strSql.Append(" WHERE ID=@In_ID ");
            System.Data.SqlClient.SqlParameter[] CmdParams = new System.Data.SqlClient.SqlParameter[] {
                    DbHelper.CreateInSqlParameter("@In_compname",DbType.AnsiString,model.compname),
                    DbHelper.CreateInSqlParameter("@In_compAbb",DbType.AnsiString,model.compAbb),
                    DbHelper.CreateInSqlParameter("@In_ERPCode",DbType.AnsiString,model.ERPCode),
                    DbHelper.CreateInSqlParameter("@In_comptype",DbType.AnsiString,model.comptype),
                    DbHelper.CreateInSqlParameter("@In_ParentID",DbType.AnsiString,model.ParentID),
                    DbHelper.CreateInSqlParameter("@In_CompanyLevel",DbType.AnsiString,model.CompanyLevel),
                    DbHelper.CreateInSqlParameter("@In_remark",DbType.AnsiString,model.remark),
                    DbHelper.CreateInSqlParameter("@In_sort",DbType.Int32,model.sort),
                    DbHelper.CreateInSqlParameter("@In_isdel",DbType.Int32,model.isdel),
                    DbHelper.CreateInSqlParameter("@In_Id",DbType.AnsiString,model.Id)};
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), CmdParams);
        }

     

        /// <summary>
        /// 根据ID删除一个实体
        /// </summary>
        public int Del(string id)
        {
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("UPDATE Sys_Company SET isdel=1 ");
            strSql.Append("WHERE in (" + id + ")");
            return DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), null);
        }
        
        /// <summary>
        /// 获取列表数据
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetList()
        {
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Company WHERE isdel=0 order by sort,compname,id desc ");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            return GetList(dr);
        }

        /// <summary>
        /// 获取列表数据(带条件的)
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetconList(string condition)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Company WHERE ");
            System.Data.SqlClient.SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString() + condition, null);
            return GetList(dr);
        }


        /// <summary>
        /// 根据ID获取一个实体
        /// </summary>
        public Model.Sys_CompanyInfo GetModel(string id)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Company WHERE id='" + id + "'");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            for (; dr.Read(); )
            {
                model = GetModel(dr);
            }
            dr.Close();
            return model;
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        private Model.Sys_CompanyInfo GetModel(System.Data.SqlClient.SqlDataReader dr)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            model.Id = dr["Id"].ToString();
            model.compname = dr["compname"].ToString();
            model.compAbb = dr["compAbb"].ToString();
            model.ERPCode = dr["ERPCode"].ToString();
            model.comptype = dr["comptype"].ToString();
            model.ParentID = dr["ParentID"].ToString();
            model.CompanyLevel = dr["CompanyLevel"].ToString();            
            model.SetType = dr["SetType"].ToString();
            model.OASetId = dr["OASetId"].ToString();
            model.remark = dr["remark"].ToString();
            if (dr["sort"].ToString() != "")
            {
                model.sort = int.Parse(dr["sort"].ToString());
            }
            if (dr["isdel"].ToString() != "")
            {
                model.isdel = int.Parse(dr["isdel"].ToString());
            }
            return model;
        }


        /// <summary>
        /// 获取一个列表
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetList(SqlDataReader dr)
        {
            List<Model.Sys_CompanyInfo> Lst = new List<Model.Sys_CompanyInfo>();
            for (; dr.Read(); )
            {
                Lst.Add(GetModel(dr));
            }
            dr.Close();
            return Lst;
        }

        

        /// <summary>
        /// 根据条件，得到数据的条数
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="condition">条件</param>
        /// <returns>返回条数</returns>
        public int GetCount(string tablename,string condition)
        {
            return DbHelper.GetCount(tablename, condition);
        }

        public SqlDataReader GetPageList(string field,string condition,int pageSize,int pageIndex,string tbNames,string sortNames,bool sortType)
        {
            return DbHelper.GetPageList(field,condition,pageSize,pageIndex,tbNames,sortNames,sortType);
 
        }

        ////////////////////////////////根据具体业务编写的方法////////////////////////////////////////////
        /// <summary>
        /// 根据公司名称获取公司数据
        /// </summary>
        /// <param name="CompName">公司名称</param>
        /// <returns>公司数据</returns>
        public Model.Sys_CompanyInfo GetCompId(string CompName)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Company WHERE  isdel=0  and compname='" + CompName + "'");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            for (; dr.Read(); )
            {
                model = GetModel(dr);
            }
            dr.Close();
            return model;
        }


        /// <summary>
        /// 根据公司ID获取ErpCode
        /// </summary>
        /// <param name="compid">公司ID</param>
        /// <returns>ErpCode</returns>
        public string GetErpCodebyCompId(string compid)
        {

            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select ERPCode from Sys_Company WHERE  isdel=0 and id= '" + compid + "'");
            return DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), null).ToString();
        }

        /// 根据公司Id获取公司名称
        /// </summary>
        /// <param name="CompId">公司Id</param>
        /// <returns>公司名称</returns>
        public string GetCompName(string CompId)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select compname from Sys_Company WHERE  isdel=0 and id= '"+ CompId  +"'");
            return DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), null).ToString();
        }

        /// 根据公司单位ERP代码获取公司名称
        /// </summary>
        /// <param name="ERPCode">公司Id</param>
        /// <returns>公司名称</returns>
        public string GetCompNameByERPCode(string ERPCode)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select compname from Sys_Company WHERE  isdel=0 and ERPCode= '" + ERPCode + "'");
            return DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), null).ToString();
        }

        /// <summary>
        /// 根据公司名称获取公司ID
        /// </summary>
        /// <param name="CompName">公司名称</param>
        /// <returns>公司ID</returns>
        public string GetCompanyId(string CompName)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select Id from Sys_Company WHERE  isdel=0 and compname='" + CompName + "'");
           // SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
           // return dr["Id"].ToString();
            return DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), null).ToString();
        }

        /// 根据公司Id获取公司ERP代码
        /// </summary>
        /// <param name="CompId">公司Id</param>
        /// <returns>公司ERP代码</returns>
        public string GetCompERPCode(string CompId)
        {
            
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select ERPCode from Sys_Company WHERE  isdel=0 and id= '" + CompId + "'");
            return DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), null).ToString();
        }
  

        /// <summary>
        /// 获取OA的油库(二级单位)
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetOACompany()
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append(" CompanyLevel='二级单位' and isdel=0 order by sort ,compname");
            return GetconList(strSql.ToString());
        }

        /// <summary>
        /// 获取HR单位
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetHRCompany()
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append(" CompanyLevel='二级单位' and isdel=0 order by sort ,compname");
            return GetconList(strSql.ToString());
        }

      

        /// <summary>
        /// 根据选中的公司ID,判断是否能删除
        /// </summary>
        /// <param name="CompIds">选中的公司ID</param>
        /// <returns>返回false 表示不能删除;返回true 表示可以删除</returns>
        public bool isCanDel(string CompIds) 
        {
            int exist = 0;
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append(" OACompId in(" + CompIds + ")  and isdel=0 ");
            exist = DbHelper.GetCount("Sys_Staff", strSql.ToString());
            if (exist >= 1) return false;
            else return true;
        }


        /// <summary>
        /// 根据用户表ID获取用户的所在单位ID
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public string GetCompIDByUserID(string UserID)
        {
            StringBuilder strSql = new StringBuilder("select c.Id from Sys_User a join Sys_Staff b on a.StaffId = b.Id join Sys_Company c on c.Id = b.OACompId ");
            strSql.Append("where a.Id = '" + UserID + "' ");
            strSql.Append(" order by c.sort");
            return DbHelper.ExecuteScalar(strSql.ToString()).ToString();
        }

        /// <summary>
        /// 根据OA公司名获取一个实体
        /// </summary>
        public Model.Sys_CompanyInfo GetOAModel(string strCompName)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Company WHERE  isdel=0  and compname = '" + strCompName + "' ");
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);
            for (; dr.Read(); )
            {
                model = GetModel(dr);
            }
            dr.Close();
            return model;
        }


        //////////////////////new//////////////////////////
        /// <summary>
        /// 获取所有级别单位列表
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetAllCompany()
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("  isdel=0 order by CompanyLevel desc, sort ,compname");
            return GetconList(strSql.ToString());
        }

        /// <summary>
        /// 获取指定级别单位列表(一级,二级)
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetLevelCompany(string strLevel)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append(" CompanyLevel ='" + strLevel + "' and isdel=0 order by sort ,compname");
            return GetconList(strSql.ToString());
        }

        /// <summary>
        /// 获取所有油库单位列表(既二级，三级单位)
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetALLYouKu()
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append(" CompanyLevel <>'一级单位' and isdel=0  and ParentID='36847E0A-5E22-4950-AB7B-CA825E19455B'  order by sort ,compname ");
            return GetconList(strSql.ToString());
        }


        /// <summary>
        /// 获取所有油库单位列表(既二级，三级单位)
        /// </summary>
        public List<Model.Sys_CompanyInfo> GetALLYouKu(bool bo)
        {
            System.Text.StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append(" CompanyLevel <>'一级单位' and isdel=0  and  ParentID<>'36847E0A-5E22-4950-AB7B-CA825E19455B' order by sort ,compname ");
            return GetconList(strSql.ToString());
        }


        /// <summary>
        /// 根据输入的单位名,单位级别 查找数据库是否存在该单位名
        /// </summary>
        /// <param name="CompName">输入的单位名</param>
        /// <param name="strLevel">单位级别</param>
        /// <returns>返回string "1"代表存在,"0"代表不存在</returns>
        public string ExistCompName(string CompName, string strLevel)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append(" compname = '" + CompName + "'  and isdel=0 and CompanyLevel ='" + strLevel + "'");
            int exist = 0;
            exist = DbHelper.GetCount("Sys_Company", strSql.ToString());
            if (exist >= 1) return "1";
            else return "0";

        } 
        
        /// <summary>
        /// 根据输入的ERP代码,单位级别 查找数据库是否存在该ERP代码
        /// </summary>
        /// <param name="CompName">输入的单位名</param>
        /// <param name="strLevel">单位级别</param>
        /// <returns>返回string "1"代表存在,"0"代表不存在</returns>
        public string ExistERPCode(string ERPCode, string strLevel)
        {
            System.Text.StringBuilder strSql = new StringBuilder();
            strSql.Append(" ERPCode = '" + ERPCode + "'  and isdel=0 and CompanyLevel ='" + strLevel + "'");
            int exist = 0;
            exist = DbHelper.GetCount("Sys_Company", strSql.ToString());
            if (exist >= 1) return "1";
            else return "0";

        }

        /// <summary>
        /// 根据单位ID 获取该单位下级单位
        /// </summary>
        /// <param name="CompName">公司名称</param>
        /// <returns>公司ID</returns>
        public List<Model.Sys_CompanyInfo> GetNextCompany(string CompId)
        {
            List<Model.Sys_CompanyInfo> Lst = new List<Model.Sys_CompanyInfo>();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select * from Sys_Company WHERE  isdel=0 and ParentID='" + CompId + "' order by sort,Compname");        
            SqlDataReader dr = DbHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null);          
            for (; dr.Read(); )
            {
                Lst.Add(GetModel(dr));
            }
            dr.Close();
            return Lst; 
        }

        public string GetShortCompName(string CompId)
        {
            Model.Sys_CompanyInfo model = new Model.Sys_CompanyInfo();
            StringBuilder strSql = new System.Text.StringBuilder();
            strSql.Append("select compAbb from Sys_Company WHERE  isdel=0 and compname= '" + CompId + "'");
            return DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), null).ToString();
        }

    }
}
 